﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using winform;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;

namespace 开单
{
    class XsdDao
    {
        XsdEntity xsdEntity = new XsdEntity();
        DataTable dt = new DataTable();
        /// <summary>
        /// 添加销售单
        /// </summary>
        /// <param name="xsEntity">销售实体类</param>
        public void Addxsxx(XsdEntity xsdEntity)
        {
            string sql = string.Empty;
            OleDbParameter[] parameters = {
                     new OleDbParameter("@xsd_code", xsdEntity.xsd_code),
                     new OleDbParameter("@xsd_kh", xsdEntity.xsd_kh),
                     new OleDbParameter("@xsd_khdh", xsdEntity.xsd_khdh),
                     new OleDbParameter("@xsd_kdr", xsdEntity.xsd_kdr),
                     new OleDbParameter("@xsd_ywy", xsdEntity.xsd_ywy),
                     new OleDbParameter("@xsd_shr", xsdEntity.xsd_shr),
                     new OleDbParameter("@xsd_shdz", xsdEntity.xsd_shdz),
                     new OleDbParameter("@xsd_kdsj", xsdEntity.xsd_kdsj),
                     new OleDbParameter("@xsd_shsj", xsdEntity.xsd_shsj)};

            sql = "insert into xs_xsd(xsd_code,xsd_kh,xsd_khdh,xsd_kdr,xsd_ywy,xsd_shr,xsd_shdz,xsd_kdsj,xsd_shsj) values " +
                    "(@xsd_code,@xsd_kh,@xsd_khdh,@xsd_kdr,@xsd_ywy,@xsd_shr,@xsd_shdz,@xsd_kdsj,@xsd_shsj)";

            DbHelperOleDb.ExecuteSql(sql, parameters);
        }

        /// <summary>
        /// 查询销售单表
        /// </summary>
        /// <param></param>
        /// <returns></returns>
        public DataTable GetXsdDt(string xsd_kh, string xsd_kdr, string xsd_shr, string xsd_sTime, string xsd_eTime)
        {
            string sql = "select xsd_code as 销售单号,xsd_kh as 客户,xsd_khdh as 客户电话,xsd_kdr as 开单人," +
                "xsd_ywy as 业务员,xsd_shr as 送货人,xsd_shdz as 送货地址,xsd_kdsj as 开单时间," +
                "xsd_shsj as 送货时间 from xs_xsd where xsd_kdsj >= @xsd_sTime and xsd_kdsj <= @xsd_eTime  ";
            OleDbParameter parameter1 = new OleDbParameter("@xsd_kh", xsd_kh);
            OleDbParameter parameter2 = new OleDbParameter("@xsd_kdr", xsd_kdr);
            OleDbParameter parameter3 = new OleDbParameter("@xsd_shr", xsd_shr);
            OleDbParameter parameter4 = new OleDbParameter("@xsd_sTime", xsd_sTime);
            OleDbParameter parameter5 = new OleDbParameter("@xsd_eTime", xsd_eTime);

            List<OleDbParameter> parameterList = new List<OleDbParameter>();
            parameterList.Add(parameter4);
            parameterList.Add(parameter5);
            if (!string.IsNullOrEmpty(xsd_kh))
            {
                sql += " and xsd_kh=@xsd_kh";
                parameterList.Add(parameter1);
            }
            if (!string.IsNullOrEmpty(xsd_kdr))
            {
                sql += " and xsd_kdr=@xsd_kdr";
                parameterList.Add(parameter2);
            }
            if (!string.IsNullOrEmpty(xsd_shr))
            {
                sql += " and xsd_shr=@xsd_shr";
                parameterList.Add(parameter3);
            }
            return DbHelperOleDb.ExecuteWithDataTable(sql, parameterList.ToArray());
        }

        /// <summary>
        /// 删除销售单
        /// </summary>
        public void DelxsxxByXsdcode(string xsd_code)
        {
            string sql = string.Empty;
            OleDbParameter[] parameters = {
                     new OleDbParameter("@xsd_code", xsd_code)};
            sql = "delete from xs_xsd where xsd_code=@xsd_code";
            DbHelperOleDb.ExecuteSql(sql, parameters);
        }



        public DataTable getInfoToExport(string[] str)
        {
            DataTable dtResult = getColumnsName();
            for (int i = 0; i < str.Length; i++)
            {
                string sql = "select xsd.xsd_code as 销售单号,xsd_kh as 客户,xsd_khdh as 客户电话,xsd_kdr as 开单人," +
                "xsd_ywy as 业务员,xsd_shr as 送货人,xsd_shdz as 送货地址,xsd_kdsj as 开单时间," +
                "xsd_shsj as 送货时间, xsmx_code as 销售明细编码,xsmx_pm as 品名,xsmx_dj as 单价," +
                "xsmx_dw as 单位,xsmx_sl as 数量,xsmx_gg as 规格 from xs_xsd xsd,xs_xsmx xsmx where xsd.xsd_code=xsmx.xsd_code and xsd.xsd_code='" + str[i] + "'";
                dt = DbHelperOleDb.ExecuteWithDataTable(sql, null);
                for (int n = 0; n < dt.Rows.Count; n++)
                {
                    dtResult.Rows.Add(dt.Rows[n].ItemArray);
                }
            }
            return dtResult;
        }

        public DataTable getColumnsName()
        {
            string sql = "select xsd.xsd_code as 销售单号,xsd_kh as 客户,xsd_khdh as 客户电话,xsd_kdr as 开单人," +
                "xsd_ywy as 业务员,xsd_shr as 送货人,xsd_shdz as 送货地址,xsd_kdsj as 开单时间," +
                "xsd_shsj as 送货时间, xsmx_code as 销售明细编码,xsmx_pm as 品名,xsmx_dj as 单价," +
                "xsmx_dw as 单位,xsmx_sl as 数量,xsmx_gg as 规格 from xs_xsd xsd,xs_xsmx xsmx where xsd.xsd_code=xsmx.xsd_code and xsd.xsd_code='abc' ";
            dt = DbHelperOleDb.ExecuteWithDataTable(sql, null);
            return dt;
        }

        public int getInfoCount()
        {
            string sql = "select count(*) from xs_xsd ";
            dt= DbHelperOleDb.ExecuteWithDataTable(sql, null);
            int i = Convert.ToInt32(dt.Rows[0][0]);
            return i;
        }

        ///// <summary>
        ///// 获得最新的
        ///// </summary>
        ///// <returns></returns>
        //public int getMaxXsdNoByMonth()             //select left(cast(('2010-09-11') as char(10)),7)    //right(cast((xsd_code) as char(12)),4) 
        //{
        //    string sql = "select xsd_code  from xs_xsd where left(cast((xsd_kdrq) as char(10)),7)=left(cast((convert(varchar(10),getdate(),121)) as char(10)),7) ";
        //    dt = DbHelperOleDb.ExecuteWithDataTable(sql, null);
        //    int i = Convert.ToInt32(dt.Rows[0][0]);
        //    return i;
        //}

        /// <summary>
        /// 获得最新的
        /// </summary>
        /// <returns></returns>
        public int getMaxXsdNoByMonth()             //select left(cast(('2010-09-11') as char(10)),7)    //right(cast((xsd_code) as char(12)),4) 
        {
            string sql = "select *  from xs_xsd  ";
            dt = DbHelperOleDb.ExecuteWithDataTable(sql, null);
            string xsdcode=null;
            int temp=0;
            int length=0;
            int resultNum = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            { 
                DateTime kdrq=Convert.ToDateTime(dt.Rows[i]["xsd_kdsj"]);
                if (kdrq.Month == DateTime.Now.Month)
                {
                    xsdcode = dt.Rows[i]["xsd_code"].ToString();
                    length = xsdcode.ToCharArray().Length;
                    temp= Convert.ToInt32(xsdcode.Substring(length - 4, 4));
                    if (resultNum < temp)
                    { 
                        resultNum = temp;
                    }
                }
            }
            if (dt.Rows.Count == 0) { return 0; }
           


            return resultNum;
        }

   
    }
}
